DROP TABLE "MATCH_RESULTS" CASCADE CONSTRAINTS;

DROP SEQUENCE MATCH_RESULTS_SEQUENCE;
CREATE SEQUENCE match_results_sequence START WITH 1 INCREMENT BY 1;


/**
 * NOTE: 
 * 1. use 3/4 character naming 
 * 2. oracle limit 30 characters for naming
 * MERCHANT_MATCH=MER_MAT 
 * MERCHANT = MER 
 * COUNTRY= COUN
 * TERMINAL=TER
 * SUBDIVISION = SUBDIV
 * LICENSE = LIC
 * DRIVER = DRIV
 * PRINCIPLE=PRIN
 */
CREATE TABLE "MATCH_RESULTS"
(	
	ID NUMBER PRIMARY KEY,
	/*1-7*/
	"APP_ID" NUMBER(10,0), 
	"CREATE_DATE" DATE,
	"EXPIRED_DATE" DATE,
	"TRANSACTION_REFERENCE_NUMBER" NUMBER(20),
	"PAGE_OFFSET" NUMBER(20),
	"TOTAL_LENGTH" NUMBER(20),
	"REFERENCE_LINK" VARCHAR(120),
	
	/*8-13*/
	"MER_MAT_NAME" VARCHAR(10),
	"MER_MAT_DOING_BUSINESS_NAME" VARCHAR(10),
	"MER_MAT_PHONE_NUMBER" VARCHAR(10),
	"MER_MAT_ADD" VARCHAR(10),
	"MER_MAT_COUN_SUBDIVISION_TAXID" VARCHAR(10),
	"MER_MAT_NATIONAL_TAXID" VARCHAR(10),
	
	/*14-18*/
	"PRI_MAT_NAME" VARCHAR(10),
	"PRI_MAT_NATIONALID" VARCHAR(10),
	"PRI_MAT_PHONE_NUMBER" VARCHAR(10),
	"PRI_MAT_ADD" VARCHAR(10),
	"PRI_MAT_DRIV_LIC" VARCHAR(10),
	
	/*19-19*/
	"TER_MER_REASON_CODE" VARCHAR(2),
	
	/*20-28*/
	"MER_NAME" VARCHAR(60),
	"MER_DOING_BUSINESS_NAME" VARCHAR(60),
	"MER_PHONE_NUMBER" VARCHAR(12),
	"MER_ADD_LINE1" VARCHAR(60),
	"MER_ADD_LINE2" VARCHAR(60),
	"MER_ADD_CITY" VARCHAR(20),
	"MER_ADD_COUN_SUBDIVI" VARCHAR(2),
	"MER_ADD_POSTAL_CODE" VARCHAR(10),
	"MER_ADD_COUN" VARCHAR(3),
	
	/*29-30*/
	"MER_COUN_SUBDIV_TAXID" VARCHAR(25),
	"MER_NATIONAL_TAXID" VARCHAR(25)
);

DROP TABLE "MATCH_MER_PRIN" CASCADE CONSTRAINTS;
CREATE TABLE "MATCH_MER_PRIN"
(	
	MER_PRIN_ID NUMBER,
	"MER_PRIN_FIRSTNAME" VARCHAR(40),
	"MER_PRIN_LASTNAME" VARCHAR(60),
	"MER_PRIN_NATIONALID" VARCHAR(25),
	"MER_PRIN_PHONE_NUMBER" VARCHAR(12),
	"MER_PRIN_ADD_LINE1" VARCHAR(60),
	"MER_PRIN_ADD_LINE2" VARCHAR(60),
	"MER_PRIN_ADD_CITY" VARCHAR(20),
	"MER_PRIN_ADD_COUN_SUBDIV" VARCHAR(2),
	"MER_PRIN_ADD_POSTAL_CODE" VARCHAR(10),
	"MER_PRIN_ADD_COUN" VARCHAR(3),
	"MER_PRIN_DRIV_LIC_NUMBER" VARCHAR(25),
	"MER_PRIN_DRIV_LIC_COUN_SUBDIV" VARCHAR(2),
	"MER_PRIN_DRIV_LIC_COUN" VARCHAR(3)
);

ALTER TABLE MATCH_MER_PRIN ADD 
      CONSTRAINT FK_PRIN_MER
      FOREIGN KEY (MER_PRIN_ID)
      REFERENCES MATCH_RESULTS(ID);

      
DROP TABLE "MATCH_MER_PRIN_TYPE" CASCADE CONSTRAINTS;
CREATE TABLE "MATCH_MER_PRIN_TYPE"
(	
	MER_PRIN_TYPE_ID NUMBER,
	"PRI_MAT_NAME" VARCHAR(10),
	"PRI_MAT_NATIONALID" VARCHAR(10),
	"PRI_MAT_PHONE_NUMBER" VARCHAR(10),
	"PRI_MAT_ADD" VARCHAR(10),
	"PRI_MAT_DRIV_LIC" VARCHAR(10)
);

ALTER TABLE MATCH_MER_PRIN_TYPE ADD 
      CONSTRAINT FK_PRIN_MER_TYPE
      FOREIGN KEY (MER_PRIN_TYPE_ID)
      REFERENCES MATCH_RESULTS(ID);


CREATE OR REPLACE TRIGGER match_results_trigger 
BEFORE INSERT ON MATCH_RESULTS 
REFERENCING NEW AS NEW
FOR EACH ROW

BEGIN
  SELECT match_results_sequence.NEXTVAL INTO :NEW.ID FROM   dual;
END;

INSERT INTO MATCH_RESULTS 
(	APP_ID, CREATE_DATE, EXPIRED_DATE, TRANSACTION_REFERENCE_NUMBER,
	PAGE_OFFSET, TOTAL_LENGTH, REFERENCE_LINK,

	MER_MAT_NAME, MER_MAT_DOING_BUSINESS_NAME, MER_MAT_PHONE_NUMBER,
	MER_MAT_ADD, MER_MAT_COUN_SUBDIVISION_TAXID, MER_MAT_NATIONAL_TAXID,
	
	TER_MER_REASON_CODE,
	
	MER_NAME, MER_DOING_BUSINESS_NAME, MER_PHONE_NUMBER, MER_ADD_LINE1,
	MER_ADD_LINE2, MER_ADD_CITY, MER_ADD_COUN_SUBDIVI, MER_ADD_POSTAL_CODE, MER_ADD_COUN,
	
	MER_COUN_SUBDIV_TAXID, MER_NATIONAL_TAXID)
VALUES 
(	10, null, null, 1,
	0, 10, 'http://google.com.vn',
	
	'EXACT', 'PHONETIC', 'NONE',
	'PHONETIC', 'EXACT', 'EXACT',
	
	'00',
	
	'test1','super', '098574596', '3 ly tu trong',
	'4 ngo quang to', 'HCM', '08', '084', '08+',
	
	'09 div', '08-'
);
INSERT INTO MATCH_MER_PRIN 
(	
	MER_PRIN_ID,
	MER_PRIN_FIRSTNAME, MER_PRIN_LASTNAME, MER_PRIN_NATIONALID, 
	MER_PRIN_PHONE_NUMBER, MER_PRIN_ADD_LINE1,
	MER_PRIN_ADD_LINE2, MER_PRIN_ADD_CITY, 
	MER_PRIN_ADD_COUN_SUBDIV, MER_PRIN_ADD_POSTAL_CODE, MER_PRIN_ADD_COUN,
	
	MER_PRIN_DRIV_LIC_NUMBER, MER_PRIN_DRIV_LIC_COUN_SUBDIV, MER_PRIN_DRIV_LIC_COUN)
VALUES 
(
	match_results_sequence.CURRVAL,
	'test_prin', 'supersuper', 'VND', 
	'093586478', '8 le quang dieu',
	'9 le quang to', 'Bien hoa', 
	'VN', '061', '06+',
	
	'258456', '04', '08'
);
INSERT INTO MATCH_MER_PRIN_TYPE 
(	
	MER_PRIN_TYPE_ID,
	PRI_MAT_NAME, PRI_MAT_NATIONALID, PRI_MAT_PHONE_NUMBER, PRI_MAT_ADD, PRI_MAT_DRIV_LIC)
VALUES 
(
	match_results_sequence.CURRVAL,
	'NONE', 'NONE', 'EXACT', 'EXACT', 'EXACT'
);

commit;
